import { Service } from 'egg';
import { Student } from '../model/Student';
interface tokenType {
  username: string;
  password: string;
  type: number;
}
interface updateType {
  id: number;
  user_id: number;
  class_id: number;
  type: number;
}
export default class StudentService extends Service {
  /**
   * 查询所有学生
   */
  public async queryStudentAll(params, decode:tokenType):Promise<object> {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    const result = await this.app.mysql.beginTransactionScope(async conn => {
      const sql = `SELECT students.id,students.class_id,classes.classname,students.user_id,users.username,students.type,students.created_time,students.updated_time from students 
                  LEFT JOIN users ON users.id = students.user_id 
                  LEFT JOIN classes ON students.class_id = classes.id
                  WHERE users.username LIKE '%${params.username.trim()}%' 
                  AND students.class_id LIKE '%${params.class_id}%' 
                  AND students.type LIKE '%${params.type}%'
                  LIMIT ${(params.current - 1) * params.pageSize}, ${params.pageSize}`;
      const studentList = await conn.query(sql);
      const pageSize = await conn.query(`
        SELECT count(*) as total FROM students 
        LEFT JOIN classes ON classes.id = students.class_id
        LEFT JOIN users ON users.id = students.user_id
        WHERE users.username LIKE '%${params.username.trim()}%' 
        AND students.class_id LIKE '%${params.class_id}%' 
        AND students.type LIKE '%${params.type}%'
      `);
      return {
        code: 0,
        message: '操作成功',
        data: { studentList, total: pageSize[0].total },
      };
    }, this.ctx);
    return result;
  }

  /**
   * 添加学生
   * @param student 学生表数据
   * @param decode token
   */
  public async addStudent(student:Student, decode:tokenType) {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    const result = await this.app.mysql.beginTransactionScope(async conn => {
      const studentArr:Student[] = await conn.select('students', { where: { user_id: student.user_id } }) as Student[];
      if (studentArr.length !== 0) {
        return {
          code: 0,
          message: '该学生已存在',
          data: [],
        };
      }
      await conn.insert('students', {
        user_id: student.user_id,
        class_id: student.class_id,
        type: 0,
        created_time: new Date(),
        updated_time: new Date(),
      });
      return {
        code: 0,
        message: '添加成功',
        data: [],
      };
    }, this.ctx);
    return result;
  }


  /**
   * 删除学生
   * @param id 学生id
   */
  public async delStudent(id:number, decode:tokenType):Promise<object> {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    const result:object = await this.app.mysql.beginTransactionScope(async conn => {
      const student:Student[] = await conn.select('students', { where: { id } }) as Student[];
      if (student.length === 0) {
        return {
          code: 1,
          message: '该学生不存在',
          data: [],
        };
      }
      await conn.delete('students', { id });
      return {
        code: 0,
        message: '删除成功',
        data: [],
      };
    }, this.ctx);
    return result;
  }

  /**
   * 修改学生
   * @param data 学生信息
   * @param decode token
   */
  public async updateStudent(data:updateType, decode:tokenType):Promise<object> {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    const result = await this.app.mysql.beginTransactionScope(async conn => {
      const students = await conn.select('students', { where: { id: data.id } }) as Array<Student>;
      if (students.length === 0) {
        return {
          code: 1,
          message: '该学生不存在',
          data: [],
        };
      }
      const update = {
        class_id: data.class_id,
        type: data.type,
      };
      await conn.update('students', update, { where: { id: data.id } });
      return {
        code: 0,
        message: '操作成功',
        data: [],
      };
    }, this.ctx);
    return result;
  }
  public async studentNum(decode:tokenType) {
    if (decode.type !== 3) {
      return {
        code: 1,
        message: '该用户无权限操作',
        data: [],
      };
    }
    const result = await this.app.mysql.beginTransactionScope(async conn => {
      const studentTypes = await conn.query('SELECT count(*) as count FROM students WHERE type = 0');
      const studentTypes1 = await conn.query('SELECT count(*) as count FROM students WHERE type = 1');
      const studentTypes2 = await conn.query('SELECT count(*) as count FROM students WHERE type = 2');
      const studentTypes3 = await conn.query('SELECT count(*) as count FROM students WHERE type = 3');
      const teacherTypes = await conn.query('SELECT count(*) as count FROM teachers WHERE type = 0');
      const teacherTypes1 = await conn.query('SELECT count(*) as count FROM teachers WHERE type = 1');
      const userTypes = await conn.query('SELECT count(*) as count FROM users WHERE type = 0');
      const userTypes1 = await conn.query('SELECT count(*) as count FROM users WHERE type = 1');
      const userTypes2 = await conn.query('SELECT count(*) as count FROM users WHERE type = 2');
      const userTypes3 = await conn.query('SELECT count(*) as count FROM users WHERE type = 3');
      return {
        code: 0,
        message: '操作成功',
        data: [
          {
            studentCommon: studentTypes[0].count,
            studentCommon1: studentTypes1[0].count,
            studentCommon2: studentTypes2[0].count,
            studentCommon3: studentTypes3[0].count,
          },
          {
            teacherCommon: teacherTypes[0].count,
            teacherCommon1: teacherTypes1[0].count,
          },
          {
            userCommon: userTypes[0].count,
            userCommon1: userTypes1[0].count,
            userCommon2: userTypes2[0].count,
            userCommon3: userTypes3[0].count,
          },
        ],
      };
    }, this.ctx);
    return result;
  }
}

